﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Collections;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;

namespace TaoBaoProject.WebModel
{
    public class ExcelOperate
    {
        public static DataSet SaveXlsToDataSet(string FullName, ArrayList SheetNames, string fileExt)
        {
            string strConn;
            if (fileExt == ".xls")
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FullName + ";Extended Properties=Excel 8.0;";//以xls文件创建连接字符串
            }
            else
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FullName + ";Extended Properties=Excel 8.0;";
            }
            DataSet ds = new DataSet();
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbDataAdapter oada = new OleDbDataAdapter("SELECT * FROM [" + SheetNames[0] + "]", strConn);
            oada.Fill(ds);//填充xls中数据到数据集
            conn.Close();
            return ds;

        }

        public static string GetFilePath(HttpRequest Request, Page page)
        {
            HttpFileCollection HFC = Request.Files;
            HttpPostedFile hf = HFC[0];
            string FilePath = page.Server.MapPath("./") + "TempXmlFile";  //服务器的临时文件夹
            string fileFullName = System.Guid.NewGuid().ToString() + System.IO.Path.GetFileName(hf.FileName); //文件名（不包括路径）
            hf.SaveAs(FilePath + "//" + fileFullName);  //先将文件存到服务器指定路径
            return (FilePath + "/" + fileFullName).Replace('/', '\\');  //包含路径
        }


        public static string GetFilePath(HttpRequest Request, string filefullname)
        {
            HttpFileCollection HFC = Request.Files;
            HttpPostedFile hf = HFC[0];
            string FilePath = filefullname + "TempXmlFile";  //服务器的临时文件夹
            string fileFullName = System.Guid.NewGuid().ToString() + System.IO.Path.GetFileName(hf.FileName); //文件名（不包括路径）
            //hf.SaveAs(FilePath + "//" + fileFullName);  //先将文件存到服务器指定路径
            return (FilePath + "/" + fileFullName).Replace('/', '\\');  //包含路径
        }

        //删除临时上传的xls文件
        public static bool DeleteXlsFile(string fullName)
        {

            if (!IsFileExists(fullName))
                return false;
            try
            {
                FileInfo fileInfo = new FileInfo(fullName);
                fileInfo.Delete();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static bool IsFileExists(string fullName)
        {
            FileInfo fileInfo = new FileInfo(fullName);
            if (fileInfo.Exists)
                return true;
            return false;
        }

        public static ArrayList GetSheetName(string filepath, string fileEx)
        {
            ArrayList al = new ArrayList();
            string strConn;
            if (fileEx == ".xls")
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            }
            else
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            }
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            return al;
        }
        public static void CreateExcel(DataSet ds, string FileName, Page p)
        {
            HttpResponse resp;
            resp = p.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";

            //定义表对象与行对象，同时用DataSet对其值进行初始化 
            DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;


            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加\n
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }

            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息 

            //逐行处理数据   
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }

                }
                resp.Write(ls_item);
                ls_item = "";

            }
            resp.End();
        }
        /// <summary>
        /// 描述：把DataTable内容导出excel并返回客户端 
        /// 作者：陈杰
        /// 时间：2013-11-1
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="header"></param>
        /// <param name="fileName"></param>
        /// <param name="mergeCellNums">要合并的列索引字典 格式：列索引-合并模式(合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项)</param>
        /// <param name="mergeKey">作为合并项的标记列索引</param>
        public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary<int, int> mergeCellNums, int? mergeKey)
        {
            System.Web.UI.WebControls.GridView gvExport = null;
            // 当前对话 
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            // IO用于导出并返回excel文件 
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;

            if (dtData != null)
            {
                // 设置编码和附件格式 
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                curContext.Response.Charset = "gb2312";
                if (!string.IsNullOrEmpty(fileName))
                {
                    //处理中文名乱码问题
                    fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
                    curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + (fileName.ToLower().EndsWith(".xls") ? fileName : fileName + ".xls")));
                }
                // 导出excel文件 
                strWriter = new System.IO.StringWriter();
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

                // 重新定义一个无分页的GridView 
                gvExport = new System.Web.UI.WebControls.GridView();
                gvExport.DataSource = dtData.DefaultView;
                gvExport.AllowPaging = false;
                //优化导出数据显示，如身份证、12-1等显示异常问题
                //gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound);

                gvExport.DataBind();
                //处理表头
                if (header != null && header.Length > 0)
                {
                    gvExport.HeaderRow.Cells.Clear();
                    gvExport.HeaderRow.Cells.AddRange(header);
                }




                // 返回客户端 
                gvExport.RenderControl(htmlWriter);
                curContext.Response.Clear();
                curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
                curContext.Response.End();
            }
        }
        /// <summary>
        /// 描述：行绑定事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                foreach (TableCell cell in e.Row.Cells)
                {
                    //优化导出数据显示，如身份证、12-1等显示异常问题
                    if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$") || Regex.IsMatch(cell.Text.Trim(), @"^\d+[-]\d+$"))
                    {
                        cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                    }
                }
            }
        }
    }
}